﻿Imports System.Data.SqlClient
Imports Control_Vuelos_BE

Public Class C_Manifiesto_AdjuntoADO
    ReadOnly MiConexion As New ConexionADO()
    Private cmd As New SqlCommand
    Private da As SqlDataAdapter
    Private dr As SqlDataReader

    Public Function pr_AgregarAdjuntoViaje(ByVal p_CMAdjunto As C_Manifiesto_Adjunto) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim sInstruccion As String = " INSERT INTO dbo.C_Manifiesto_Adjunto(CodViaje,CodAdjunto,NomAdjunto,FileAdjunto,FchAdjunto,SizAdjunto) VALUES (@CodViaje,@CodAdjunto,@NomAdjunto,@FileAdjunto,@FchAdjunto,@SizAdjunto)"
            Try
                cmd = New SqlCommand(sInstruccion, cn)
                cmd.Transaction = oTran
                cmd.Parameters.AddWithValue("@CodViaje", p_CMAdjunto.CodViaje)
                cmd.Parameters.AddWithValue("@CodAdjunto", p_CMAdjunto.CodAdjunto)
                cmd.Parameters.AddWithValue("@NomAdjunto", p_CMAdjunto.NomAdjunto)
                cmd.Parameters.AddWithValue("@FileAdjunto", p_CMAdjunto.FileAdjunto)
                cmd.Parameters.AddWithValue("@FchAdjunto", p_CMAdjunto.FchAdjunto)
                cmd.Parameters.AddWithValue("@SizAdjunto", p_CMAdjunto.SizAdjunto)
                cmd.ExecuteNonQuery()
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("C_Manifiesto_AdjuntoADO (pr_AgregarAdjuntoViaje) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function fn_EliminarAdjunto(ByVal p_CodViaje As String, ByVal p_CodAdjunto As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim sInstruccion As String = "DELETE FROM dbo.C_Manifiesto_Adjunto WHERE CodViaje = @CodViaje AND CodAdjunto = @CodAdjunto "
            Try
                cmd = New SqlCommand(sInstruccion, cn)
                cmd.Transaction = oTran
                cmd.Parameters.AddWithValue("@CodViaje", p_CodViaje)
                cmd.Parameters.AddWithValue("@CodAdjunto", p_CodAdjunto)
                cmd.ExecuteNonQuery()
                oTran.Commit()
                Return True
            Catch ex As Exception
                MsgBox("C_Manifiesto_AdjuntoADO (pr_EliminarAdjunto) --> " & ex.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using

    End Function

    Public Function fn_NumeroSiguiente(ByVal p_CodViaje As String) As Integer
        Try
            Dim nCod As Integer = 0
            Dim sql As String = "SELECT ISNULL(MAX(cast(CodAdjunto as int )),0)+1 as nrosgte FROM C_Manifiesto_Adjunto WHERE CodViaje = @CodViaje "
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                cn.Open()
                Try
                    cmd = New SqlCommand(sql, cn)
                    cmd.Parameters.AddWithValue("@CodViaje", p_CodViaje)
                    nCod = cmd.ExecuteScalar()
                    Return nCod
                Catch myException As Exception
                    Return 0
                End Try
            End Using
        Catch myException As Exception
            MsgBox("C_Manifiesto_AdjuntoADO (fn_NumeroSiguiente) --> " & myException.Message, MsgBoxStyle.Critical)
            Return 0
        End Try
    End Function

    Public Function fn_MostrarAdjunto(ByVal p_CodViaje As String, ByVal p_CodAdjunto As String) As C_Manifiesto_Adjunto
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Dim _CMAdjunto As New C_Manifiesto_Adjunto
            Using cn
                cn.Open()
                Dim bytes() As Byte
                bytes = Nothing
                cmd = New SqlCommand
                cmd.Connection = cn
                cmd.CommandText = "pr_MostrarAdjunto"
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@pCodViaje", p_CodViaje)
                cmd.Parameters.AddWithValue("@pCodAdjunto", p_CodAdjunto)
                dr = cmd.ExecuteReader()
                While dr.Read
                    bytes = CType(dr.GetValue(dr.GetOrdinal("FileAdjunto")), Byte())
                    _CMAdjunto.CodViaje = dr.GetValue(dr.GetOrdinal("CodViaje"))
                    _CMAdjunto.CodAdjunto = dr.GetValue(dr.GetOrdinal("CodAdjunto"))
                    _CMAdjunto.NomAdjunto = dr.GetValue(dr.GetOrdinal("NomAdjunto"))
                    _CMAdjunto.FchAdjunto = dr.GetValue(dr.GetOrdinal("FchAdjunto"))
                    _CMAdjunto.SizAdjunto = dr.GetValue(dr.GetOrdinal("SizAdjunto"))
                    _CMAdjunto.FileAdjunto = bytes
                End While
                dr.Close()
                cmd.Dispose()
                Return _CMAdjunto
            End Using
        Catch ex As Exception
            dr.Close()
            cmd.Dispose()
            Throw ex
        End Try
    End Function

    Public Function ListarManifiestoAdjunto(ByVal vCodman As String) As DataTable
        Dim dts As New DataSet
        Dim ada As SqlDataAdapter
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            cmd.Connection = cn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "sp_FileManifiestoAdjunto"
            cmd.Parameters.Clear()
            cmd.Parameters.Add(New SqlParameter("@pCodViaje", SqlDbType.Char, 12))
            cmd.Parameters("@pCodViaje").Value = vCodman
            ada = New SqlDataAdapter(cmd)
            ada.Fill(dts, "C_Adjunto")
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
        Return dts.Tables("C_Adjunto")
    End Function

End Class
